package top.withwings.bonus.bonus.repo;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.thymeleaf.util.StringUtils;
import top.withwings.bonus.bonus.pojo.Good;
import top.withwings.bonus.bonus.pojo.OnSaleForm;
import top.withwings.bonus.bonus.pojo.bonus.rule.OnSaleBonusRule;
import top.withwings.bonus.common.pojo.PageParam;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

@Repository
public class OnSaleFormRepo {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    public int create(OnSaleForm form) {
        String sql = " insert into on_sale_form (rule_id,good_id,batch_nos,start,end) values (:ruleId,:goodId,:batchNos,:start,:end)";
        KeyHolder keyHolder = new GeneratedKeyHolder();
        MapSqlParameterSource mapSqlParameterSource = new MapSqlParameterSource();
        mapSqlParameterSource.addValue("ruleId", form.getRule().getId());
        mapSqlParameterSource.addValue("goodId", form.getGood().getId());
        mapSqlParameterSource.addValue("batchNos", StringUtils.join(form.getBatchNos(), ","));
        mapSqlParameterSource.addValue("start", form.getStart());
        mapSqlParameterSource.addValue("end", form.getEnd());
        namedParameterJdbcTemplate.update(sql, mapSqlParameterSource, keyHolder);
        return keyHolder.getKey().intValue();
    }

    public void delete(int formId) {
        String sql = " delete from on_sale_form where id = ?";
        jdbcTemplate.update(sql, formId);

    }

    public void update(OnSaleForm form) {
        String sql = " update on_sale_form set rule_id =:ruleId, good_id = :goodId , batch_nos = :batchNos,  start=:start, end=:end where id=:id";
        MapSqlParameterSource mapSqlParameterSource = new MapSqlParameterSource();
        mapSqlParameterSource.addValue("ruleId", form.getRule().getId());
        mapSqlParameterSource.addValue("goodId", form.getGood().getId());
        mapSqlParameterSource.addValue("batchNos", StringUtils.join(form.getBatchNos(), ','));
        mapSqlParameterSource.addValue("start", form.getStart());
        mapSqlParameterSource.addValue("end", form.getEnd());
        mapSqlParameterSource.addValue("id", form.getId());
        namedParameterJdbcTemplate.update(sql, mapSqlParameterSource);
    }

    public OnSaleForm findById(int formId) {
        String sql = " SELECT " +
                " g.id as good_id, " +
                " g.code as good_code, " +
                " g.name as good_name, " +
                " g.specification as good_specification, " +
                " g.standard_price as good_standard_price, " +
                " f.id as id, " +
                " f.batch_nos " +
                " f.start as start, " +
                " f.end as end,  " +
                " r.id as rule_id,  " +
                " r.name as rule_name  " +
                " from on_sale_form f " +
                "   inner join good g on f.good_id = g.id " +
                "   inner join bonus_rule r on f.rule_id = r.id " +
                " where f.id = ?  ";

        try {
            return jdbcTemplate.queryForObject(sql, new OnSaleFormMapper(), formId);
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    public List<OnSaleForm> search(PageParam pageParam, Integer goodId, Date date) {

        String sql =  " SELECT " +
                " g.id as good_id, " +
                " g.code as good_code, " +
                " g.name as good_name, " +
                " g.specification as good_specification, " +
                " g.standard_price as good_standard_price, " +
                " f.id as id, " +
                " f.batch_nos, " +
                " f.start as start, " +
                " f.end as end,  " +
                " r.id as rule_id,  " +
                " r.name as rule_name  " +
                " from on_sale_form f " +
                "   inner join good g on f.good_id = g.id " +
                "   inner join bonus_rule r on f.rule_id = r.id " +
                " where 1 = 1  ";
        if (goodId != null) {
            sql += " and  g.id = :goodId ";
        }

        if (date != null) {
            sql += " and start<= :date  and end >= :date ";
        }

        sql+=" limit :start, :length ";

        Map<String, Object> paramMap = new HashMap<>();
        paramMap.put("goodId", goodId);
        paramMap.put("date", date);
        paramMap.put("start", pageParam.getStart());
        paramMap.put("length", pageParam.getLength());
        return namedParameterJdbcTemplate.query(sql, paramMap, new OnSaleFormMapper());
    }

    public int count(Integer goodId, Date date) {
        String sql = "select count(*) from on_sale_form f where 1 =1 ";
        if (goodId != null) {
            sql += " and  f.good_id = :goodId";
        }

        if (date != null) {
            sql += " and f.start<= :date  and f.end >= :date ";
        }

        Map<String, Object> paramMap = new HashMap<>();
        paramMap.put("goodId", goodId);
        paramMap.put("date", date);

        return namedParameterJdbcTemplate.queryForObject(sql, paramMap, Integer.class);
    }

    public int countByRule(int ruleId) {
        String sql = "select count(*) from on_sale_form f on f.good_id = g.id where rule_id = ?";

        return jdbcTemplate.queryForObject(sql,Integer.class,ruleId);
    }



    private static class OnSaleFormMapper implements RowMapper<OnSaleForm> {

        @Override
        public OnSaleForm mapRow(ResultSet rs, int rowNum) throws SQLException {

            Good good = new Good();

            good.setId(rs.getInt("good_id"));
            good.setCode(rs.getString("good_code"));
            good.setSpecification(rs.getString("good_specification"));
            good.setName(rs.getString("good_name"));
            good.setStandardPrice(rs.getDouble("good_standard_price"));

            int ruleId = rs.getInt("rule_id");
            String ruleName = rs.getString("rule_name");

            OnSaleBonusRule rule = new OnSaleBonusRule(ruleId, ruleName, new LinkedHashMap<>());

            OnSaleForm form = new OnSaleForm();
            form.setId(rs.getInt("id"));
            form.setRule(rule);
            form.setGood(good);
            form.setBatchNos(Arrays.asList(StringUtils.split(rs.getString("batch_nos"), ",")));
            form.setStart(rs.getTimestamp("start"));
            form.setEnd(rs.getTimestamp("end"));


            return form;
        }
    }


}